const pool = require('../modules/tools/getPool');

const createDatabase = function(connection){
    return new Promise((resolve, reject)=>{
    	console.log("hahaha")
		connection.query('CREATE DATABASE medicine DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;', (error, rows, fields)=>{
			if (error) {
				reject(error,1)
			}
			resolve(rows)
		})
    })
}

const useDatabase = function(connection){
    return new Promise((resolve, reject)=>{
		connection.query('USE medicine;', (error, rows, fields)=>{
			if (error) {
				reject(error,2)
			}
			resolve(rows)
		})
    })
}

const createAdmin = function(connection){
    return new Promise((resolve, reject)=>{
		connection.query('CREATE TABLE `medicine`.`admin` ( `account` VARCHAR(20) NOT NULL, `password` VARCHAR(20) NOT NULL, PRIMARY KEY (`account`), UNIQUE INDEX `account_UNIQUE` (`account` ASC));', (error, rows, fields)=>{
			if (error) {
				reject(error)
			}
			resolve(rows)
		})
    })
}

const createClerk = function(connection){
    return new Promise((resolve, reject)=>{
		connection.query('CREATE TABLE `medicine`.`clerk` ( `id` INT NOT NULL AUTO_INCREMENT, `account` VARCHAR(20) NOT NULL, `password` VARCHAR(20) NOT NULL, `department` VARCHAR(20) NULL, `name` VARCHAR(20) NULL, `type` VARCHAR(20) NOT NULL, `able` TINYINT NOT NULL DEFAULT 1, `createtime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE INDEX `account_UNIQUE` (`account` ASC));', (error, rows, fields)=>{
			if (error) {
				reject(error,3)
			}
			resolve(rows)
		})
    })
}

const createMedicine = function(connection){
    return new Promise((resolve, reject)=>{
		connection.query('CREATE TABLE `medicine`.`medicine` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL, `amout` INT NOT NULL DEFAULT 0, `price` FLOAT NOT NULL, `introduce` TEXT(500) NOT NULL, `imgurl` VARCHAR(45) NULL, `producetime` INT NOT NULL, `abletime` INT NOT NULL, `code` VARCHAR(30) NOT NULL, `company` VARCHAR(45) NOT NULL, `address` VARCHAR(45) NOT NULL, `phone` VARCHAR(20) NOT NULL, `able` TINYINT NOT NULL DEFAULT 1, `type` VARCHAR(20) NULL, `createtime` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE INDEX `name_UNIQUE` (`name` ASC));', (error, rows, fields)=>{
			if (error) {
				reject(error,4)
			}
			resolve(rows)
		})
    })
}

const createBill = function(connection){
    return new Promise((resolve, reject)=>{
		connection.query('CREATE TABLE `medicine`.`bill` ( `id` INT NOT NULL AUTO_INCREMENT, `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `type` VARCHAR(20) NOT NULL, `medicine_id` INT NOT NULL, `clerk_id` INT NOT NULL, `amout` INT NOT NULL, `message` VARCHAR(45) NULL, `remarks` VARCHAR(45) NULL, PRIMARY KEY (`id`), CONSTRAINT `fk_medicine_id` FOREIGN KEY (`medicine_id`) REFERENCES `medicine`.`medicine` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_clerk_id` FOREIGN KEY (`clerk_id`) REFERENCES `medicine`.`clerk` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION); ', (error, rows, fields)=>{
			if (error) {
				reject(error,5)
			}
			resolve(rows)
		})
    })
}

pool.getConnection((err, connection)=>{
	if (err) {
		console.error(err);
		return;
	}

	const start = async function(){

		// await createDatabase(connection);
		await useDatabase(connection);
		await createAdmin(connection);
		await createClerk(connection);
		await createMedicine(connection);
		await createBill(connection);

	}

	start().then(v=>{

		console.log("success");

	})
	.catch((e,n)=>{
		console.log(e);
		console.log(n);
	})

});